Business Intelligence Project
12/01/2021
Teacher : Mister VASSIDIKI FOFANA
Team 1 : Sami Heddid Errati Saad Gilbert Delphin Mayouran
Varathalingam Nihale Ben Driouch
Promotion 2022, Team 1
INTRODUCTION
TO BUSINESS
INTELLIGENCE
Sami HEDDID
Errati SAAD
Gilbert DELPHIN
Mayouran
VARATHALINGAM
Nihale BEN DRIOUCH
Deadline :
12/01/2021
For :
M.VASSIDIKI
FOFANA
BUSINESS INTELLIGENCE REPORT
2
SUMMARY | | |
BUSINESS INTELLIGENCE PROJECT
I Introduction
II Project’s Description
III Team Organization
IV ER Diagram
V Relational Model
VI SSIS / SSAS
VII SSRS
VIII - Conclusion
1
2
7
6
5
4
3
8
BUSINESS INTELLIGENCE REPORT
3
Screenshot showing what it looked like in the beginning.
I - INTRODUCTION
First and foremost, Team 1 would like to thank mister VASSIDIKI FOFANA for
his help throughout this project and his pedagogy during our TPs which helped us
greatly understand the principles of Business Intelligence. We are, indeed, in a crucial
year where understanding foundations and grounds of BI are key not only to an IT
engineer but also the enterprise he works in.
So, the course as well as the one teaching it is very important to us and we would
like to note it by showing our appreciation for it.
Being students in the major BUSINESS INTELLIGENCE, we’ll be constantly
manipulating data inside a database. Nevertheless, we must point out that we must
analyze these datas the best way possible to make the best solution possible. Every IT
company is involved with BI and DBs, so this project is a good practice to get us started.
NB : Way of Working
As agile methodologies can tend to be as important as BI itself inside a company, we decided to work
with the framework SCRUM, using the Atlassian Jira Software. That way, we can synchronize ourselves
and work more efficiently and finish our tasks in the deadline set.
1
BUSINESS INTELLIGENCE REPORT
4
II PROJECT’S DESCRIPTION
Let’s summarize what is happening first.
The objective of the project is to enhance the international positioning of
EFREI PARIS. For this, the school signed partnerships with several major schools and
universities around the globe. it is about having a reporting tool that will allow him to
follow the students during their schooling but also in their professional life through the
alumni association of which he remains the sponsor. Using a robust data model and
management KPIs, the tool should also allow to follow various things like course
programming, monitoring the availability of teachers, end of year assessments and
exams, payment of tuition fees or salaries of school staffs.
III TEAM ORGANIZATION
As this is quite the consequent project and we are considered as IT experts, we needed to
have roles for the team to achieve tasks more efficiently in agile.
PRODUCT OWNER : Sami Heddid. Hes the one updating the product backlog,
as well as defining which functionalities the team needed to do in priorities.
CUBE DEVELOPER : Gilbert Delphin. Hes the one creating the cube and
importing data from the database to have it in the data source.
DATABASE CREATOR : Mayouran Varathalingam. Hes the one creating the
database in SQL Management Studio.
REPORT WRITER : Nihale Ben Driouch. Shes the one writing the report.
DATA MODELS CREATOR : Saad Errati. Hes the one creating the DB models.
2
3
1
2
3
4
5
BUSINESS INTELLIGENCE REPORT
5
ER DIAGRAM
It must be pointed however, that there were some tasks which the team did together to be
sure no one was left behind : for example, creating the .txt files for the database, choosing
which tables to create and why, with which attributes, etc.
IV ER DIAGRAM
An entity relationship diagram (ERD) shows the relationships of entity sets
stored in a database. An entity in this context is an object, a component of data. An en-
tity set is a collection of similar entities. These entities can have attributes that define
its properties. By defining the entities, their attributes, and showing the relationships
between them, an ER diagram illustrates the logical structure of databases.
ER diagrams are used to sketch out the design of a database. In our case, it was
very important for us to model our database as we are dealing with a big structure. And it
is partially thanks to modelization that the school could make better business decisions.
4
BUSINESS INTELLIGENCE REPORT
6
Reasoning behind choices :
The file project contains all the tables used in this project and based of the E/A
diagram.
Our main table is Student : we chose as a primary key to this table the
attribute StudentID, as explained in the description given by the professor,
Mister Meunier wants to follow the students during their schooling, this is why
we created the table Student with their name, the sex, the date of birth and the
grade as attributes.
Also he want to follow them in their professional life through the alumni
association, so we create a table Alumni with the name of their company.
We needed a table Course to allow us to see the courses of students but also
professors with their salaries directly related to the AdministrationStaff, with StaffID,
the primary Key.
The teacher table allows us to track the availability of teachers and thus allow
them to carry out their course.
We have also created a table with tuition fees for students with as primary key,
TuitionFeesId
M. Meunier wishes to know the need to open school canteens on his
campuses in London, Pekin and New York. He is therefore interested in the
attendance rate of the canteen of the Ivry and Marseille campuses to validate
an opening on another campus.
It is for this reason that we decided to create a Canteen and canteen Attendance
table in order to have the number of students each month in a canteen, this one is
directly connected to the campus with as primary keys Attendance ID.
BUSINESS INTELLIGENCE REPORT
7
RELATIONAL MODEL
V RELATIONAL MODEL
The purpose of the relational model is to provide a declarative method for
specifying data and queries : users directly state what information the database
contains and what information they want from it, and let the database management
system software take care of describing data structures for storing the data and retrieval
procedures for answering queries. The relational model is the primary method for
organizing and maintaining data today in information systems because it is so flexible
and accessible. It organizes data in two-dimensional tables called relations, with rows
and columns.
5
BUSINESS INTELLIGENCE REPORT
8
SSIS CONTROL FLOW
VI SSIS / SSAS
a) SSIS Part
SQL Server Integration Services (SSIS) is a component of the Microsoft SQL
Server database software that can be used to perform a broad range of data mi-
gration tasks.
SSIS is a platform for data integration and workflow applications. It features
a data warehousing tool used for data extraction, transformation, and loading
(ETL). The tool may also be used to automate maintenance of SQL Server da-
tabases and updates to multidimensional cube data.
In SSIS, we needed to take care of two major tasks : Control Flow and Data
Flow. Control flow defines a workflow of tasks to be executed, such as follows :
Indeed, as were working with 13 tables, we needed to link them all to the
database with Connection Managers.
6
BUSINESS INTELLIGENCE REPORT
9
SSIS DATA FLOW FOR COURSE TABLE
.txt FILE FORMAT OF THE COURSE TABLE
After that, we needed to input data flow tasks for every tables : they had to have a
Source, and a Destination for them to be browsed properly. Heres an example with the
Course table :
NB : We must point out that we dealt with our tables by creating them in .txt format.
Subsequently, when asked for the source of the table, we browsed in with tables we
created before-hand. Heres an example with the same Course table :
BUSINESS INTELLIGENCE REPORT
10
FLAT FILE CONNECTION MANAGER EDITOR, WHERE WE BROWSE OUR .txt FILES
b) SSAS Part :
After storing all of our data in exploitable form, we could start the
Multidimensional Analysis. To do this, SSAS comes in handy.
Microsoft SQL Server Analysis Services, SSAS, is an online analytical
processing (OLAP) and data mining tool in Microsoft SQL Server. SSAS is used
as a tool by organizations to analyze and make sense of information possibly
spread out across multiple databases, or in disparate tables or
files. Microsoft has included a number of services in SQL Server related
to business intelligence and data warehousing. These services include
Integration Services, Reporting Services and Analysis Services. Analysis
Services includes a group of OLAP and data mining capabilities and comes in
two flavors - Multidimensional and Tabular.
Furthermore, and as reminded in the Projects Description, we needed to
follow :
1) Course Programming
2) Monitoring of teachers
3) Monitoring of asessments
4) Payment of tuition fees
5) Payment of salaries
BUSINESS INTELLIGENCE REPORT
11
DIMENSIONS OF THE MULTIDIMENSIONAL
CUBE
ALUMNI RESULTS
Hence why, as we are working with a Multidimensional Cube, we decided
the create dimensions based on these datas :
After deploying the cube, it appears on Management Studio. Now, we can
analyze whichever results we wants :
BUSINESS INTELLIGENCE REPORT
12
ASSESSMENTS RESULTS FILTERED BY EXAM (NB : Those who dont have grades is because they
didnt pass the exam yet.)
ASSESSMENTS RESULTS FILTERED BY PROJECT
PAYMENT OF SALARIES RESULT
BUSINESS INTELLIGENCE REPORT
13
TEACHER AVAILABILITY RESULT
PAYMENT OF TUITION FEES
BUSINESS INTELLIGENCE REPORT
14
REPORTS FILES
VI SSRS
After that, all was left was to do the reporting part. We must point out that we tried
to implement it with PowerBI, but we couldnt get the database to work as we cant
restore a database from a recent version to a previous one. Instead, we decided to work
with SSRS.
SQL Server Reporting Services (SSRS) is a server-based report generating software
system from Microsoft. It is part of a suite of Microsoft SQL Server services, including
SSAS (SQL Server Analysis Services) and SSIS (SQL Server Integration Services).
Administered via a Web interface, it can be used to prepare and deliver a variety of in-
teractive and printed reports. The SSRS service provides an interface into Microsoft Vi-
sual Studio so that developers as well as SQL administrators can connect to SQL data-
bases and use SSRS tools to format SQL reports in many complex ways. It also provides
a 'Report Builder' tool for less technical users to format SQL reports of lesser com-
plexity.
SSRS competes with Crystal Reports and other business intelligence tools.
Here, we created 7 reports from the Data Source of our databases :
7
BUSINESS INTELLIGENCE REPORT
15
ALUMNI REPORT
ASSESSMENTS REPORT (NB : Those who dont have grades is because they didnt pass the
assessment yet.)
BUSINESS INTELLIGENCE REPORT
16
CANTEEN ATTENDANCE REPORT
TEACHER AVAILABILITY REPORT
BUSINESS INTELLIGENCE REPORT
17
PAYMENT OF SALARIES REPORT
PAYMENT OF TUITION FEES REPORT
Through these reports and throughout the entire process of analyzing our data, we
decided to choose measures which we found the most interesting and observed them
through different dimensions. After that, we modelized those with differents kind of
charts. To do this in SSRS, we needed to click on the toolbox that would let us customize
our reports :
BUSINESS INTELLIGENCE REPORT
18
CHART CUSTOMIZATION IN SSRS
BUSINESS INTELLIGENCE REPORT
19
VII CONCLUSION
This project was very interesting as it was the first one to formally introduce us to Business
Intelligence. It’s a good thing we work in a team of five as we knew exactly how to divide
tasks and we always helped one another when there was some issues regarding the
modelling, developing or even working in agile : communication was indeed key.
What was most interesting is that multiple answers exist as to how to operate inside MSSQL
and how to modelize tasks (how to even do BI), so we talked a lot about how we should
approach things.
Again, we would like to thank mister VASSIDIKI FOFANA for this help throughout his course.
Teaching and following courses can somehow be difficult with the pandemic but he always
answered our questions efficiently and we highly appreciate it.
(NB : Every file we showed in the report is in the .zip file, dont hesitate to read the readme.txt
file to make sure you have everything in hands.
Sami Heddid
Gilbert Delphin
Mayouran Varathalingam
Saad Errati
Nihale Ben Driouch
Promotion 2022, Efrei Paris
8